package dao

import javax.inject.Inject
import controllers._
import models.Tables._
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import slick.jdbc.JdbcProfile
import tool.Pojo.{LongData, PageData}
import tool.Tool

import scala.concurrent.Future
import scala.concurrent.ExecutionContext.Implicits.global

/**
 * Created by yz on 2017/6/1.
 */
class BrowseLongRnaDao @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends
  HasDatabaseConfigProvider[JdbcProfile] {

  import profile.api._

  type MyRow = BrowseLongRnaRow
  val table = BrowseLongRna

  def insert(longrnageneannotation: MyRow): Future[Unit] = db.run(table +=
    longrnageneannotation).map(_ => ())

  def insertAll(longrnageneannotation: Seq[MyRow]): Future[Unit] = db.run(table ++=
    longrnageneannotation).map(_ => ())

  def deleteAll(): Future[Unit] = db.run(table.delete).map(_ => ())

  def test() = db.run(table.schema.drop.asTry)

  def selectByGeneNames(names: Seq[String]) = {
    db.run(table.filter(_.geneName.inSetBind(names)).result)
  }

  def selectByGeneName(name: String) = {
    db.run(table.filter(_.geneName === name).result.head)
  }

  def selectAllGeneNames: Future[Seq[String]] = {
    db.run(table.map(_.geneName).distinct.result)
  }

  def selectAllSampleTypes = {
    db.run(table.map(_.sampleType).distinct.result.map(_.toList))
  }

  def selectAllDiffGroup = {
    db.run(table.map(_.diffGroup).distinct.result.map(_.toList))
  }

  def selectAllTissue = {
    db.run(table.map(_.tissueSpecificity).distinct.result.map(_.toList))
  }

  def selectAll(ids: List[String]) = db.run(table.
    filter(_.geneName.inSetBind(ids)).result)

  def selectAll(data: LongData, pageData: PageData) = {
    val filterData = table.filter { x =>
      data.geneType match {
        case "both" => x.geneType === x.geneType
        case y => x.geneType === y
      }
    }.filterOpt(data.samples) { (x, ys) =>
      val allB = ys.map { y =>
        x.sampleType.like(s"%${y}%")
      }
      val b = data.samplesMethod match {
        case "union" => allB.reduceLeft((t, z) => t || z)
        case "inter" => allB.reduceLeft((t, z) => t && z)
      }
      b
    }.filterOpt(data.tissue) { (x, y) =>
      x.tissueSpecificity === y
    }.filterOpt(data.dfMin) { (x, y) =>
      x.detectionFrequency >= y
    }.filterOpt(data.dfMax) { (x, y) =>
      x.detectionFrequency <= y
    }.filterOpt(data.diffSamples) { (x, ys) =>
      val allB = ys.map { y =>
        x.diffGroup.like(s"%${y}%")
      }
      val b = data.diffSamplesMethod match {
        case "union" => allB.reduceLeft((t, z) => t || z)
        case "inter" => allB.reduceLeft((t, z) => t && z)
      }
      b
    }.filterOpt(pageData.search) { (x, y) =>
      val columns = List("gene_name", "tissue_specificity", "sample_type", "diff_group")
      columns.map { columnName =>
        x.getStrColumn(columnName).like(s"%${y}%")
      }.reduceLeft((x, y) => x || y)
    }
    val intColumnNames = List("related")
    val doubleColumnNames = List("detection_frequency", "tumor_mean", "benign_mean", "health_mean", "urine_mean",
      "csf_mean", "bile_mean")
    val sortData = data.sort match {
      case None => filterData
      case Some(x) =>
        val columnName = Tool.getTrueColumnName(x)
        val order = data.order
        columnName match {
          case x if intColumnNames.contains(columnName) => order match {
            case "asc" => filterData.sortBy { y =>
              y.getIntColumn(columnName)
            }
            case "desc" => filterData.sortBy { x =>
              x.getIntColumn(columnName).desc
            }
          }
          case x if doubleColumnNames.contains(columnName) => order match {
            case "asc" => filterData.sortBy { y =>
              y.getDoubleColumn(columnName)
            }
            case "desc" => filterData.sortBy { x =>
              x.getDoubleColumn(columnName).desc
            }
          }
          case _ => order match {
            case "asc" => filterData.sortBy { y =>
              y.getStrColumn(columnName)
            }
            case "desc" => filterData.sortBy { x =>
              x.getStrColumn(columnName).desc
            }
          }
        }
    }
    val finalData = sortData
    val countF = finalData.length.result
    val resultF = finalData.drop(pageData.offset).take(pageData.limit).result
    val f = countF.zip(resultF)
    db.run(f)
  }

  //  def selectSamples: Future[Seq[String]] = {
  //    db.run(table.map(_.samplegroup).distinct.result).
  //      map(x => x.flatMap(_.split(",")).distinct.filter(_ != ""))
  //  }

  def dropTable(): Future[Unit] = {
    val sqlu = DBIO.seq(
      sqlu"""
          DROP TABLE IF EXISTS longrnageneannotation;
        """)
    db.run(sqlu).map(_ => ())
  }

  def createTable(): Future[Unit] = {
    val sqlu = DBIO.seq(
      sqlu"""
        CREATE TABLE `longrnageneannotation` (
              `geneName` VARCHAR(255) NOT NULL,
              `fullName` VARCHAR(255) NOT NULL,
              `chromoseome` VARCHAR(255) NOT NULL,
             `start` INT(11) NOT NULL,
              `end` INT(11) NOT NULL,
              `strand` VARCHAR(255) NOT NULL,
              `length` VARCHAR(255) NOT NULL,
              `geneType` VARCHAR(255) NOT NULL,
              `genecards` VARCHAR(255) NOT NULL,
              `relatedCircs` TEXT NOT NULL,
              `detectionFrequency` DOUBLE(255,3) NOT NULL,
              `expressionRank` VARCHAR(255) NOT NULL,
              `specifiExpressionTissue` VARCHAR(255) NOT NULL,
              `specificityScore` VARCHAR(255) NOT NULL,
              `sampleGroup` VARCHAR(255) NOT NULL,
              `diffGroup` VARCHAR(255) NOT NULL,
              `validation` VARCHAR(255) NOT NULL,
              PRIMARY KEY (`geneName`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        """)
    db.run(sqlu).map(_ => ())
  }

}
